﻿/*----------------------------------------------------------------
    // Copyright (C) 2009 江苏华招网
    // 版权所有。 
    // 作者：庄金冬 
    //
    // 文件名：DAL_Hospital.cs
    // 文件功能描述：医疗机构数据访问逻辑
    // 创建标识：庄金冬2009-03-06
    //
    // 修改标识：
    // 修改描述：
    //
    // 修改标识：
    // 修改描述：
    //
    // 审核人：
    // 审核时间：
    // 审核说明：
 //----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using HZ.Data;
using System.Text;
using HZ.DrugTrade.Model;
using HZ.Common;
using System.Data;
using System.Data.SqlClient;
namespace HZ.DrugTrade.DAL
{
    /// <summary>
    /// 医疗机构数据访问逻辑
    /// </summary>
    public class DAL_Hospital
    {

        /// <summary>
        /// 获取最新可用医疗机构编号
        /// </summary>
        /// <returns>最新可用医疗机构编号</returns>
        public static string GetNewHospitalID()
        {
            /*
             add by sinodier 20100827
             * 生成最新可用的医疗机构编号，通过分析库，发现5000以内只用了800不到的号，所以选择可以使用这个区间生成号，足够用
             */
//            string sql_GetNewHospitalID = @"select case when (max(cast(SUBSTR(HospitalID,2,4) as int))+1)<1000 then 'H0'||cast((max(cast(SUBSTR(HospitalID,2,4) as int))+1) AS VARCHAR2(4000)) else 'H'||cast((max(cast(SUBSTR(HospitalID,2,4) as int))+1) AS VARCHAR2(4000)) end HospitalID from TB_Hospital
//where  cast(SUBSTR(HospitalID,2,4) as int)<5000";
            string sql_GetNewHospitalID = @"select case
         when (max(cast(SUBSTR(HospitalID, 3, 6) as int)) + 1) < 1000 then
          'QH000' || cast((max(cast(SUBSTR(HospitalID, 3, 6) as int)) + 1) AS
                       VARCHAR2(4000))
         else
          'QH' || cast((max(cast(SUBSTR(HospitalID, 3, 6) as int)) + 1) AS
                      VARCHAR2(4000))
       end HospitalID
  from TB_Hospital
 where cast(SUBSTR(HospitalID, 3, 6) as int) < 5000";
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql_GetNewHospitalID).Rows[0][0].ToString();
        }


        /// <summary>
        /// 获取所有医疗机构
        /// </summary>
        /// <returns>所有医疗机构</returns>
        public static IList<Hospital> GetAllHospital()
        {
            string sql_GetAllHospital = @"SELECT * FROM TB_Hospital ";
            return HZ.Data.DAL_Helper.GetModelList<Hospital>(sql_GetAllHospital, null);
        }

        /// <summary>
        /// 获取所有医疗机构等级详细
        /// </summary>
        /// <returns></returns>
        public static IList<HospitalInfo> GetHospitalinfoLevelDetail()
        {
            string sql_GetHospitalLevelDetail = @"select LevelDetail from  TB_HospitalInfo group by LevelDetail";
            return HZ.Data.DAL_Helper.GetModelList<HospitalInfo>(sql_GetHospitalLevelDetail, null);

        }
        /// <summary>
        /// 获取所有医疗机构等级
        /// </summary>
        /// <returns></returns>
        public static IList<HospitalInfo> GetHospitalinfoLevels()
        {
            string sql_GetHospitalLevelDetail = @"select Levels from TB_HospitalInfo group by Levels";
            return HZ.Data.DAL_Helper.GetModelList<HospitalInfo>(sql_GetHospitalLevelDetail, null);

        }
        /// <summary>
        /// 根据医疗机构等级获取医疗机构ID
        /// </summary>
        /// <param name="LevelDetail"></param>
        /// <returns></returns>
        public static IList<HospitalInfo> GetHospitalIDByLevelDetail(string LevelDetail)
        {
            string[] str = LevelDetail.Split(',');
            string sql_GetHospitalIDByLevelDetail = string.Format("select HospitalID,LevelDetail from TB_HospitalInfo where LevelDetail='{1}' and Levels='{0}'", str[0], str[1]);
            return (List<HospitalInfo>)HZ.Data.DAL_Helper.GetModelList<HospitalInfo>(sql_GetHospitalIDByLevelDetail, null);
        }

        /// <summary>
        /// 编辑医疗机构
        /// </summary>
        /// <returns>True:成功;False:失败</returns>
        public static bool UpdateHospital(Hospital hospital)
        {
            int retValue = 0;
            try
            {
                #region 改版前
                //SqlParameter[] paras = new SqlParameter[7];
                ////TB_Hospital【HospitalID,HospitalName,HospitalAddress,ContactPerson,ContactTel,AreaID】
                ////医疗机构编号
                //paras[0] = new SqlParameter("@in_HospitalID", string.IsNullOrEmpty(hospital.HospitalID) ? "" : hospital.HospitalID);
                ////医疗机构名称
                //paras[1] = new SqlParameter("@in_HospitalName", string.IsNullOrEmpty(hospital.HospitalName) ? "" : hospital.HospitalName);
                ////医疗机构地址
                //paras[2] = new SqlParameter("@in_HospitalAddress", string.IsNullOrEmpty(hospital.HospitalAddress) ? "" : hospital.HospitalAddress);
                ////联系人
                //paras[3] = new SqlParameter("@in_ContactPerson", string.IsNullOrEmpty(hospital.ContactPerson) ? "" : hospital.ContactPerson);
                ////联系电话
                //paras[4] = new SqlParameter("@in_ContactTel", string.IsNullOrEmpty(hospital.ContactTel) ? "" : hospital.ContactTel);
                ////所属区域
                //paras[5] = new SqlParameter("@in_AreaID", hospital.Area == null ? "1" : hospital.Area.AreaID);
                ////输入参数处理结果(0,添加失败;1,添加成功;)
                //paras[6] = new SqlParameter("@out_success", SqlDbType.Int);
                //paras[6].Direction = ParameterDirection.Output;

                ////执行存储过程
                //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Hospital_Update", paras);

                //retValue = int.Parse(paras[6].Value.ToString()); 
                #endregion




                #region 改版后


                //                @"UPDATE [dbo].[TB_Hospital] SET 
                //					[AreaID]=@in_AreaID,
                //					[HospitalName]=@in_HospitalName,
                //					[HospitalAddress]=@in_HospitalAddress,
                //					[ContactPerson]=@in_ContactPerson,
                //					[ContactTel]=@in_ContactTel
                //				WHERE [HospitalID] =  @in_HospitalID";


                SqlParameter[] paras = new SqlParameter[7];
                //TB_Hospital【HospitalID,HospitalName,HospitalAddress,ContactPerson,ContactTel,AreaID】
                //医疗机构编号
                paras[0] = new SqlParameter("@in_HospitalID", string.IsNullOrEmpty(hospital.HospitalID) ? "" : hospital.HospitalID);
                //医疗机构名称
                paras[1] = new SqlParameter("@in_HospitalName", string.IsNullOrEmpty(hospital.HospitalName) ? "" : hospital.HospitalName);
                //医疗机构地址
                paras[2] = new SqlParameter("@in_HospitalAddress", string.IsNullOrEmpty(hospital.HospitalAddress) ? "" : hospital.HospitalAddress);
                //联系人
                paras[3] = new SqlParameter("@in_ContactPerson", string.IsNullOrEmpty(hospital.ContactPerson) ? "" : hospital.ContactPerson);
                //联系电话
                paras[4] = new SqlParameter("@in_ContactTel", string.IsNullOrEmpty(hospital.ContactTel) ? "" : hospital.ContactTel);
                //所属区域
                paras[5] = new SqlParameter("@in_AreaID", hospital.Area == null ? "1" : hospital.Area.AreaID);
                //输入参数处理结果(0,添加失败;1,添加成功;)
                paras[6] = new SqlParameter("@out_success", SqlDbType.Int);
                paras[6].Direction = ParameterDirection.Output;

                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.Text, "usp_Hospital_Update", paras);

                retValue = int.Parse(paras[6].Value.ToString());
                #endregion
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_Hospital).FullName, "更新医疗机构不成功", ex);

            }

            return retValue == 1;
        }

        /// <summary>
        /// 删除医疗机构
        /// </summary>
        /// <param name="hospital">医疗机构</param>
        /// <returns>0.删除失败;1.删除成功;2.已被使用不能删除;</returns>
        public static int DeleteHospital(Hospital hospital)
        {
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[2];

                //TB_Hospital【HospitalID,IsUsing】
                //医疗机构编号
                paras[0] = new SqlParameter("@in_HospitalID", string.IsNullOrEmpty(hospital.HospitalID) ? "" : hospital.HospitalID);

                //输入参数处理结果(0.删除失败;1.删除成功;2.已被使用不能删除;)
                paras[1] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[1].Direction = ParameterDirection.Output;

                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Hospital_Delete", paras);

                retValue = int.Parse(paras[1].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_Hospital).FullName, "删除医疗机构不成功", ex);

            }

            return retValue;
        }

        /// <summary>
        /// 物理上删除医疗机构
        /// </summary>
        /// <param name="HospitalID">医疗机构ID</param>
        /// <param name="type">删除类型：1 退货和结算 2入库和购 3医疗机构信息</param>
        /// <returns></returns>
        public static int Delete_physicsHospital(String HospitalID, string type)
        {

            //            StringBuilder sb = new StringBuilder();
            //            string strSQL = null;
            //            var sh = HZ.Data.DbHelperFactory.Create();
            //            sh.BeginTrans();
            //            try
            //            {
            //                SqlParameter[] paras = new SqlParameter[1];

            //                //医疗机构编号
            //                paras[0] = new SqlParameter("@in_HospitalID", HospitalID);
            //                if (string.IsNullOrEmpty(type))
            //                {
            //                    throw new Exception("医疗机构ID无效");
            //                }
            //                else if (type == "1")
            //                {
            //                    #region  医疗机构采购 退货
            //                    sb.Append(@"  --删除医疗机构退货详细
            //                   Delete TB_ReturnMerchandiseDetail Where  
            //                             Exists(Select ReturnSerialID From TB_ReturnMerchandise  where HospitalID=@in_HospitalID  and TB_ReturnMerchandise.ReturnSerialID=TB_ReturnMerchandiseDetail.ReturnSerialID)
            //                  --删除退货单
            //                    Delete TB_ReturnMerchandise Where HospitalID=@in_HospitalID");
            //                }
            //                else if (type == "2")
            //                {
            //                    strSQL = @"   IF  Exists(Select top 1 * From view_ReturnMerchandise where HospitalID=@in_HospitalID)  Select  2 Else Select 0 ";
            //                    int i = (int)sh.ExecuteScalar(CommandType.Text, strSQL, paras);
            //                    if (i > 0)  //说明有采购单
            //                    {
            //                        sh.RollTrans();
            //                        return 2;
            //                    }
            //                    else
            //                    {
            //                        sb.Append(@"   --删除发票明细信息
            //		             DELETE FROM   [TB_InvoiceDetail]		WHERE  Exists(Select * from [TB_Invoice] Where HospitalID=@in_HospitalID and	[TB_Invoice].InvoiceSerialID=[TB_InvoiceDetail].InvoiceSerialID)
            //	                    DECLARE @InvoicePaySerialID   TABLE ([ID] VARCHAR(64) NOT NULL)  --定义一个表变量
            //                     --医疗机构结算单号
            //                    Insert Into @InvoicePaySerialID
            //                    select distinct InvoicePaySerialID 
            //                     FROM   [TB_Invoice] WHERE  HospitalID=@in_HospitalID and InvoicePaySerialID is not Null
            //                       	--删除发票信息
            //	                   	DELETE FROM   [TB_Invoice] WHERE  HospitalID=@in_HospitalID
            //                        --删除发票结算详情
            //                       DELETE FROM   [TB_InvoicePay] 	WHERE  InvoicePaySerialID IN(Select ID from @InvoicePaySerialID)
            //	             ");
            //                    }
            //                }
            //                else if (type == "3")
            //                {
            //                    strSQL = @"   IF  Exists(Select top 1 * From view_Invoice where HospitalID=@in_HospitalID)  Select  2 Else Select 0 ";
            //                    int i = (int)sh.ExecuteScalar(CommandType.Text, strSQL, paras);
            //                    if (i > 0)  //说明有采购单
            //                    {
            //                        sh.RollTrans();
            //                        return 2;
            //                    }
            //                    else
            //                    {
            //                        sb.Append(@"--入库
            //		                        Delete  from [TB_Distribution]  Where Exists (Select orderDetailID from TB_PurchaseOrder a,TB_PurchaseOrderDetail b 
            //                                                                                    where a.HospitalID=@in_HospitalID
            //                                                                                       and a.orderID=b.orderID 
            //                                                                                       and b.OrderDetailID=[TB_Distribution].OrderDetailID)
            //                              --采购详细              
            //                                 Delete From TB_PurchaseOrderDetail    Where Exists (SElect orderDetailID from TB_PurchaseOrder 
            //                                                                                                                              where TB_PurchaseOrder.HospitalID=@in_HospitalID
            //                                                                                                                                  and TB_PurchaseOrder.orderID=TB_PurchaseOrderDetail.OrderID )
            //                            --采购单
            //                                   Delete  from TB_PurchaseOrder   where HospitalID=@in_HospitalID");
            //                    }
            //                    #endregion
            //                }
            //                else if (type == "4")
            //                {
            //                    #region
            //                    strSQL = @"   IF  Exists(Select top 1 * From View_PurchaseOrder where HospitalID=@in_HospitalID)  Select  2 Else Select 0 ";
            //                    int i = (int)sh.ExecuteScalar(CommandType.Text, strSQL, paras);
            //                    if (i > 0)  //说明有采购单
            //                    {
            //                        sh.RollTrans();
            //                        return 2;
            //                    }
            //                    else
            //                    {
            //                        sb.Append("Delete  from TB_HospitalProcureCatalog Where HospitalID=@in_HospitalID;");
            //                        sb.Append("Delete  from TB_HospitalProcureCatalogCompanyPs Where HospitalID=@in_HospitalID;");
            //                        sb.Append(" Delete TB_FeedBack where sender=@in_HospitalID Or Reciever=@in_HospitalID ; ");
            //                        sb.Append(" Delete TB_Message where sender=@in_HospitalID Or Reciever=@in_HospitalID;  ");
            //                        sb.Append(" Delete TB_Notice where sender=@in_HospitalID Or Reciever=@in_HospitalID;  ");
            //                    }

            //                    #endregion
            //                }
            //                else if (type == "5")
            //                {
            //                    //删除医疗机构相关数据
            //                    strSQL = @"   IF  Exists(Select top 1 * From TB_HospitalProcureCatalog where HospitalID=@in_HospitalID)  Select  2 Else Select 0 ";
            //                    int i = (int)sh.ExecuteScalar(CommandType.Text, strSQL, paras);
            //                    if (i > 0)  //说明有采购单
            //                    {
            //                        sh.RollTrans();
            //                        return 2;
            //                    }
            //                    else
            //                    {

            //                        sb.Append(" delete from TB_Role_User  where UserID=@in_HospitalID ;");
            //                        sb.Append("delete from TB_UserRule  where UserID=@in_HospitalID ;");
            //                        sb.Append(" delete from TB_User  where UserID In(Select UserID from tb_role_user Where OrgID=@in_HospitalID) ;");
            //                        sb.Append(" Delete From TB_Hospital Where HospitalID=@in_HospitalID ;");
            //                        sb.Append(" Delete From TB_HospitalInfo Where HospitalID=@in_HospitalID ;");
            //                        sb.Append(" Delete from TB_KeyOperationLog where Operator=@in_HospitalID ;");
            //                    }

            //                }
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string sql = string.Format(@"
                      declare V_Type number;
                             V_Count number;
                            begin
                                V_Type:={0};
                                if V_Type=1 then
                                    Delete TB_ReturnMerchandiseDetail Where  
                                         Exists(Select ReturnSerialID From TB_ReturnMerchandise  where HospitalID=@in_HospitalID  
                                         and TB_ReturnMerchandise.ReturnSerialID=TB_ReturnMerchandiseDetail.ReturnSerialID) ;
                                         Delete TB_ReturnMerchandise Where HospitalID=@in_HospitalID;
                                elsif  V_Type=2 then
                                   V_Count:=0;
                                   Select count(*) into V_Count From view_ReturnMerchandise where HospitalID=@in_HospitalID;
                                   if  V_Count=0 then
                                          DELETE FROM   TB_InvoiceDetail    WHERE  Exists(Select * from TB_Invoice Where HospitalID=@in_HospitalID 
                                           and  TB_Invoice.InvoiceSerialID=TB_InvoiceDetail.InvoiceSerialID);
                                          DELETE FROM   TB_InvoicePay   WHERE  InvoicePaySerialID 
                                                IN( select distinct InvoicePaySerialID 
                                                 FROM   TB_Invoice WHERE  HospitalID=@in_HospitalID
                                                  and InvoicePaySerialID is not Null);
                                         DELETE FROM  TB_Invoice WHERE  HospitalID=@in_HospitalID;
                                   end if;
                                elsif  V_Type=3 then
                                  V_Count:=0;
                                  Select count(*) into V_Count From view_Invoice where HospitalID=@in_HospitalID;
                                   if V_Count=0 then
                                    Delete  from TB_Distribution  Where Exists (Select orderDetailID from TB_PurchaseOrder a,TB_PurchaseOrderDetail b 
                                                     where a.HospitalID=@in_HospitalID  and a.orderID=b.orderID  and b.OrderDetailID=TB_Distribution.OrderDetailID);
                                                                                                                 
                                    Delete From TB_PurchaseOrderDetail    Where Exists (SElect orderDetailID from TB_PurchaseOrder 
                                         where TB_PurchaseOrder.HospitalID=@in_HospitalID and TB_PurchaseOrder.orderID=TB_PurchaseOrderDetail.OrderID );
                                    Delete  from TB_PurchaseOrder   where HospitalID=@in_HospitalID;                                                                                          
                                  end if;                                                                         
                                elsif V_Type=4 then
                                   V_Count:=0;
                                   Select count(*) into V_Count From View_PurchaseOrder where HospitalID=@in_HospitalID;
                                   if V_Count=0 then
                                      Delete  from TB_HospitalProcureCatalog Where HospitalID=@in_HospitalID;
                                      Delete  from TB_HOSPROCURECATALOGCOMPS Where HospitalID=@in_HospitalID;
                                      Delete TB_FeedBack where sender=@in_HospitalID Or Reciever=@in_HospitalID ;
                                      Delete TB_Message where sender=@in_HospitalID Or Reciever=@in_HospitalID;
                                      Delete TB_Notice where sender=@in_HospitalID Or Reciever=@in_HospitalID;
                                   end if;
                                elsif V_Type=4 then
                                   V_Count:=0;
                                   Select count(*) into V_Count From TB_HospitalProcureCatalog where HospitalID=@in_HospitalID ;
                                   if V_Count=0 then
                                     delete from TB_Role_User where UserID=@in_HospitalID ;
                                     delete from TB_UserRule  where UserID=@in_HospitalID ;
                                     delete from TB_User  where UserID In(Select UserID from tb_role_user Where OrgID=@in_HospitalID) ;
                                     Delete From TB_Hospital Where HospitalID=@in_HospitalID ;
                                     Delete From TB_HospitalInfo Where HospitalID=@in_HospitalID ;
                                     Delete from TB_KeyOperationLog where Operator=@in_HospitalID ;
                                   end if;
                                   
                               end if;
                                
                            end;
                            ", type);
                h.ExecuteNonQuery(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_HospitalID", HospitalID));
                h.CommitTrans();
                Flag = 1;
            }
            catch (Exception ee)
            {
                h.RollTrans();
                Flag = 0;
            }
            return Flag;
        }

        /// <summary>
        /// 添加医疗机构
        /// </summary>
        /// <param name="hospital">医疗机构</param>
        /// <returns>0.添加失败;1.添加成功;2.已经存在;</returns>
        public static int AddHospital(Hospital hospital)
        {

            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[7];
                //TB_Hospital【HospitalID,HospitalName,HospitalAddress,ContactPerson,ContactTel,AreaID】
                //医疗机构编号
                paras[0] = new SqlParameter("@in_HospitalID", string.IsNullOrEmpty(hospital.HospitalID) ? "" : hospital.HospitalID);
                //医疗机构名称
                paras[1] = new SqlParameter("@in_HospitalName", string.IsNullOrEmpty(hospital.HospitalName) ? "" : hospital.HospitalName);
                //医疗机构地址
                paras[2] = new SqlParameter("@in_HospitalAddress", string.IsNullOrEmpty(hospital.HospitalAddress) ? "" : hospital.HospitalAddress);
                //联系人
                paras[3] = new SqlParameter("@in_ContactPerson", string.IsNullOrEmpty(hospital.ContactPerson) ? "" : hospital.ContactPerson);
                //联系电话
                paras[4] = new SqlParameter("@in_ContactTel", string.IsNullOrEmpty(hospital.ContactTel) ? "" : hospital.ContactTel);
                //所属区域
                paras[5] = new SqlParameter("@in_AreaID", hospital.Area == null ? "1" : hospital.Area.AreaID);
                //输入参数处理结果(0.添加失败;1.添加成功;2.已经存在;)
                paras[6] = new SqlParameter("@out_success", SqlDbType.Int);
                paras[6].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Hospital_Add", paras);

                retValue = int.Parse(paras[6].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_Hospital).FullName, "添加医疗机构信息不成功", ex);

            }

            return retValue;
        }



        /// <summary>
        /// 条件医疗机构查询
        /// </summary>
        /// <param name="hospital">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<Hospital> GetHospitalByWhere(HZ.DrugTrade.Model.Query.Hospital hospital, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.Hospital>(hospital, true);
            //string sql =string.Format("SELECT b.IsUsing,a.* FROM View_HospitalArea  a inner join TB_User b on a.HospitalID=b.UserID where   1=1 {0}   ", where);
            //        StringBuilder builder = new StringBuilder ();
            //        if (hospital.IsUing==1)
            //{
            //     where.a
            //}
            string sql = string.Format(@"SELECT * FROM View_HospitalArea WHERE 1=1 {0} ", where);
             HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
            //HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            //IList<Hospital> list = HZ.Data.DAL_Helper.GetHospitalsFromDataTable(dt);
            IList<Hospital> list = HZ.Data.DAL_Helper.CommonFillList<Hospital>(dt);
            return list;
        }

        /// <summary>
        /// 获取所有未注册医疗机构
        /// </summary>
        /// <returns>所有未注册医疗机构</returns>
        public static IList<Hospital> GetAllUnregHospital()
        {
            string sql_GetAllUnregHospital = @"SELECT DISTINCT HospitalID,
	AreaID,
	HospitalName,
	HospitalAddress,
	ContactPerson,
	ContactTel FROM (
	SELECT     *
FROM         TB_Hospital LEFT OUTER JOIN
                      TB_Role_User ON TB_Hospital.HospitalID = TB_Role_User.OrgID AND TB_Role_User.IsAdmin = 1  AND TB_Role_User.RoleID = '01'
) TT
WHERE TT.UserID IS NULL";
            return HZ.Data.DAL_Helper.GetModelList<Hospital>(sql_GetAllUnregHospital, null);
        }

        public static int MarkLinkTHospitalInfo(HospitalInfo hospitalinfo, Hospital hospital)
        {
            string sql = string.Format("UPDATE TB_HospitalInfo SET HospitalID = '{0}' WHERE HospitalName = '{1}'", hospital.HospitalID, hospitalinfo.HospitalName);
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
            throw new NotImplementedException();
        }

        public static IList<HospitalInfo> GetRegedHospitalInfo(HZ.DrugTrade.Model.Query.HospitalInfo where, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format("SELECT * FROM TB_HospitalInfo WHERE HospitalID IS NOT NULL {0}", HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true));


            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HospitalInfo>(des.GetSpecailPage());
            throw new NotImplementedException();
        }

        public static IList<HospitalInfo> GetUnregedHopialInfo(HZ.DrugTrade.Model.Query.HospitalInfo where, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format("SELECT * FROM TB_HospitalInfo WHERE HospitalID IS NULL {0}", HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true));


            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HospitalInfo>(des.GetSpecailPage());
        }

        public static int AuditHospital(IList<HospitalInfo> hospitallist)
        {
            var sh = HZ.Data.DbHelperFactory.Create();
            try
            {
                sh.BeginTrans(); //锁定表
                // 插入医疗机构

                // 获取最大的UserID
                System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"\d+");
                string sql = "SELECT MAX(UserID) FROM TB_USER WHERE UserID LIKE 'H%'";
                object obj = sh.ExecuteScalar(sql);
                string maxid = string.Empty;
                int id = 0;
                if (Convert.IsDBNull(obj))
                {
                    maxid = "H0001";
                }
                else
                {
                    maxid = obj.ToString();
                    id = int.Parse(regex.Match(maxid).Value);
                }
                // 获取医疗机构ID
                sql = "SELECT MAX(HospitalID) FROM TB_Hospital";
                obj = sh.ExecuteScalar(sql);
                string maxhospitalid = "";
                int hid = 0;

                if (Convert.IsDBNull(obj))
                {
                    maxhospitalid = "H0001";
                }
                else
                {
                    maxhospitalid = obj.ToString();
                    hid = int.Parse(regex.Match(maxhospitalid).Value);
                }
                // 插入帐号
                for (int i = 0; i < hospitallist.Count; i++)
                {
                    id++;
                    hid++;
                    // 插入医疗机构

                    string hospitalid = "H" + hid.ToString("D4");
                    string userid = "H" + id.ToString("D4");
                    Model.Hospital host = new Hospital()
                    {
                        HospitalID = hospitalid,
                        HospitalName = hospitallist[i].HospitalName,
                        AreaID = hospitallist[i].AreaID,
                        ContactPerson = hospitallist[i].ContactPerson,
                        HospitalAddress = hospitallist[i].Address,
                        ContactTel = hospitallist[i].TelNo
                    };
                    //sql = HZ.Data.DAL_Helper.GetInsertSql(host);
                    //sh.ExecuteNonQuery(sql);
                    HZ.Data.DAL_Helper.OperateTableSQL<Model.Hospital>(host, HZ.Data.TableOperator.Insert, sh);
                    Model.User_Tail user = new User_Tail()
                    {
                        UserID = "H" + id.ToString("D4"),
                        IsAdmin = true,
                        IsUsing = true,
                        UserPassword = HZ.Fun2.GetMd5("999999"),
                        UserName = host.HospitalName
                    };
                    //sql = HZ.Data.DAL_Helper.GetInsertSql(user);
                    //sh.ExecuteNonQuery(sql);
                    HZ.Data.DAL_Helper.OperateTableSQL<Model.User_Tail>(user, HZ.Data.TableOperator.Insert, sh);
                    // 更新关联
                    sql = string.Format("UPDATE TB_HospitalInfo SET HospitalID = '{0}' WHERE HospitalName = '{1}' ", hospitalid, hospitallist[i].HospitalName);
                    sh.ExecuteNonQuery(sql);


                    // 插入用户表机构关联

                    Model.Role_User ru = new Role_User()
                    {
                        IsAdmin = true,
                        OrgID = hospitalid,
                        RoleID = "01",
                        UserID = userid
                    };
                    //sql = HZ.Data.DAL_Helper.GetInsertSql(ru);
                    //sh.ExecuteNonQuery(sql);
                    HZ.Data.DAL_Helper.OperateTableSQL<Model.Role_User>(ru, HZ.Data.TableOperator.Insert, sh);
                    // 插入用户权限

                    sql = string.Format(@"INSERT INTO TB_UserRule (UserID,OperatingRuleID,IsEffective)
SELECT '{0}', OperatingRuleID ,1 FROM TB_Role_Rule WHERE RoleID = '01' AND IsUsing = 1", userid);
                    sh.ExecuteNonQuery(sql);

                }


                // 插入医疗机构帐号关联
                // 插入帐号权限
                sh.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {

                throw ee;
            }
            return 0;
            throw new NotImplementedException();
        }

        public static int DeleteHospitalInfo(IList<HospitalInfo> list)
        {
            var sh = HZ.Data.DbHelperFactory.Create();
            try
            {
                sh.BeginTrans();
                foreach (var item in list)
                {
                    string sql = string.Format("DELETE FROM TB_HospitalInfo WHERE HospitalName = '{0}'", item.HospitalName);
                    sh.ExecuteNonQuery(sql);

                }
                sh.CommitTrans();
                return 1;

            }
            catch (Exception ee)
            {
                sh.RollTrans();
                return 0;
            }

        }

        /// <summary>
        /// 根据医疗机构ID获取医疗机构其它信息
        /// </summary>
        /// <param name="hospitalid"></param>
        /// <returns></returns>
        public static HospitalInfo GetHospitalInfoByID(string hospitalid)
        {
            try
            {
                Model.HospitalInfo mdlHosp = new HospitalInfo();
                string sql = string.Format("SELECT * FROM TB_HospitalInfo WHERE rownum <=1 and UPPER(HospitalID) = UPPER('{0}')", hospitalid);
                DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
                if (dt.Rows.Count > 0)
                {
                    mdlHosp = HZ.Data.DAL_Helper.CommonFill<HospitalInfo>(dt.Rows[0]);
                }
                else
                {
                    mdlHosp = null;
                }
                return mdlHosp;
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        public static IList<HospitalInfo> GetAllHopialInfo(HZ.DrugTrade.Model.Query.HospitalInfo where, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format("SELECT * FROM TB_HospitalInfo WHERE 1=1 {0}", HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true));


            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HospitalInfo>(des.GetSpecailPage());
        }

        public static DataTable GetAllHopialInfo2(HZ.DrugTrade.Model.Query.HospitalInfo where, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format(@"select *  from  (SELECT     TB_User.UserID, TB_User.UserName, TB_User.LastLoginTime, TB_HospitalInfo.*,TB_Area.areaname
FROM         TB_User INNER JOIN
                      TB_HospitalInfo ON TB_User.UserID = TB_HospitalInfo.HospitalID 
INNER JOIN  TB_Area ON TB_HospitalInfo.AreaID = TB_Area.AreaID )  xx WHERE 1=1 {0}", HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true));


            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        public static IList<HospitalInfo> GetAllUnInitializeHopialInfo(string where, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format(@"select *  from  (SELECT   HospitalID
      ,AreaID
      ,HospitalName
      ,Address
      ,Levels
      ,ContactPerson
      ,TelNo
      ,CellNo
      ,Email
      ,LevelDetail
      ,Code
      ,Nature
      ,EconomicType
      ,Organizers
      ,Affiliation
      ,Beds
      ,IsCooperative
      ,IsInsurance
      ,IsOld
      ,Expenditure
FROM         TB_User INNER JOIN
                      TB_HospitalInfo ON TB_User.UserID = TB_HospitalInfo.HospitalID  and  (len(UserPassword)<>32 or UserPassword is null) )  xx WHERE 1=1 {0}", where);


            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HospitalInfo>(des.GetSpecailPage());
        }

    }
}
